package org.util;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import org.data.Event;
import org.data.EventORace;
import org.data.EventRunRace;
import org.data.ORace;
import org.data.Race;
import org.data.RunRace;

/**
 *
 * @author Dmitriy.Krasnov
 */
public class EventDAO  {
    public static final Event EMPTY_EVENT = new Event();
    public static final RunRace EMPTY_RUN_RACE = new RunRace();
    public static final ORace EMPTY_O_RACE = new ORace();

    /* Add Event to database.
     *
     * @param ev event to be added to database
     *
     */
    public static Integer addEvent(Event ev) {
        if (ev.id == null) {
            ev.id = GeneralDAO.getAvailableId(EMPTY_EVENT);
        }
        GeneralDAO.getDb().insert(ev);
        return ev.id;
    }

    /** Returns event for user by id
     *
     * @param userId id of user for which event should be returned
     * @param eventId id of event which should be returned
     * @return Event with specefied id and user id or null if event with that id and user id not presented
     */
    public static Event getUserEventById(Integer userId, Integer eventId){
        List<Event> list = GeneralDAO.getDb().from(EMPTY_EVENT).where(EMPTY_EVENT.user_id).is(userId).and(EMPTY_EVENT.id).is(eventId).select();
        if(!list.isEmpty()){
            return list.get(list.size() - 1);
        }
        return null;
    }

    /** Returns all events for user
     *
     * @param user_id user is for which should be returned all events
     * @return all events for one user
     */
    public static List getUserEvents(Integer userId) {
        return GeneralDAO.getDb().from(EMPTY_EVENT).where(EMPTY_EVENT.user_id).is(userId).and(EMPTY_EVENT.deleted).is(0).orderBy(EMPTY_EVENT.id).select();
    }

    /* Returns list of events by userId and by date.
     *
     * @param userId Id of user for which events should be returned
     * @param date date by which we should select events
     *
     */
    public static List<Event> getUserEventsByDate(Integer userId, Timestamp date) {
        return GeneralDAO.getDb().from(EMPTY_EVENT).where(EMPTY_EVENT.date).is(date).and(EMPTY_EVENT.user_id).is(userId).orderBy(EMPTY_EVENT.date).select();
    }

    /* Returns list of events by userId and by date (bigger or equal to presented).
     *
     * @param userId Id of user for which events should be returned
     * @param date date by which we should select events
     *
     */
    public static List<Event> getUserEventsBiggerEqualDate(Integer userId, Timestamp date) {
        return GeneralDAO.getDb().from(EMPTY_EVENT).where(EMPTY_EVENT.date).biggerEqual(date).and(EMPTY_EVENT.user_id).is(userId).orderBy(EMPTY_EVENT.date).select();
    }

    /* Returns list of events by userId and by date (smaller or equal to presented).
     *
     * @param userId Id of user for which events should be returned
     * @param date date by which we should select events
     *
     */
    public static List<Event> getUserEventsSmallerEqualDate(Integer userId, Timestamp date) {
        return GeneralDAO.getDb().from(EMPTY_EVENT).where(EMPTY_EVENT.date).smallerEqual(date).and(EMPTY_EVENT.user_id).is(userId).orderBy(EMPTY_EVENT.date).select();
    }

    /* Returns list of events by userId and between two dates.
     * If firstDate less than lastDate uses lastDate as firstDate and firstDate as lastDate.
     * Method use only Date not time for getting events. And for last date of interval used date of the next day after lastDate
     *
     * @param userId Id of user for which events should be returned
     * @param firstDate date from which starts needed interval
     * @param lastDate date by which ends needed interval (realy using date of the next day)
     *
     */
    public static List<Event> getUserEventsBetweenDates(Integer userId, Timestamp firstDate, Timestamp lastDate){
        if(!firstDate.before(lastDate)){
            Timestamp tempDate = firstDate;
            firstDate = lastDate;
            lastDate = tempDate;
        }
        return GeneralDAO.getDb().from(EMPTY_EVENT).where(EMPTY_EVENT.date).biggerEqual(firstDate).and(EMPTY_EVENT.date).smaller(lastDate).and(EMPTY_EVENT.user_id).is(userId).and(EMPTY_EVENT.deleted).is(0).orderBy(EMPTY_EVENT.date).select();
    }

    public static List getAllEvents() {
        return GeneralDAO.getDb().from(EMPTY_EVENT).orderBy(EMPTY_EVENT.id).select();
    }

    public static List<Event> getAllEventsPartial(int count, int startNumber, String sortColumn, String sortType) {
        Connection connection = GeneralDAO.getConnection();
        List<Event> result = new ArrayList<Event>();
        String sql = "SELECT * FROM EVENT ORDER BY " + sortColumn + " " + sortType + " LIMIT " + count + " OFFSET " + startNumber;
        try {
            PreparedStatement statement = connection.prepareStatement(sql);
            ResultSet resultSet = statement.executeQuery();
            while (resultSet.next()) {
                int id = resultSet.getInt(1);
                int user_id = resultSet.getInt(2);
                String name = resultSet.getString(3);
                String place = resultSet.getString(4);
                Timestamp timestamp = resultSet.getTimestamp(5);
                Event event = new Event(id, user_id, name, place, timestamp);
                result.add(event);
            }
        } catch (SQLException ex) {
            Logger.getLogger(EventDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return result;
    }
    
    public static void deleteEvent(Integer id) {
        List<Event> list = GeneralDAO.getDb().from(EMPTY_EVENT).where(EMPTY_EVENT.id).is(id).select();
        if (list.size() > 0) {
            for(Event temp : list) {
                temp.deleted = 1;
                GeneralDAO.getDb().from(EMPTY_EVENT).where(EMPTY_EVENT.id).is(id).delete();
                GeneralDAO.getDb().insert(temp);
            }
        }
    }
    
    public static void addRunRace(Integer eventId, RunRace rr) {
        Integer rrId = GeneralDAO.getAvailableId(EMPTY_RUN_RACE);
        rr.id = rrId;
        GeneralDAO.getDb().insert(rr);
        
        EventRunRace err = new EventRunRace(eventId, rrId);
        GeneralDAO.getDb().insert(err);
    }

    public static void addORace(Integer eventId, ORace or) {
        Integer orId = GeneralDAO.getAvailableId(EMPTY_O_RACE);
        or.id = orId;
        GeneralDAO.getDb().insert(or);

        EventORace err = new EventORace(eventId, orId);
        GeneralDAO.getDb().insert(err);
    }

    public static List<ORace> getAllORacesByEventId(Integer eventId){
        List<ORace> result = new ArrayList<ORace>();
        EventORace eOR = new EventORace();
        List<EventORace> allORaces = GeneralDAO.getDb().from(eOR).where(eOR.event_id).is(eventId).select();
        for(EventORace eORNext: allORaces){
            List<ORace> tempList = GeneralDAO.getDb().from(EMPTY_O_RACE).where(EMPTY_O_RACE.id).is(eORNext.runrace_id).select();
            if(!tempList.isEmpty()){
                result.add(tempList.get(tempList.size()-1));
            }
        }
        return result;
    }

    public static List<RunRace> getAllRunRacesByEventId(Integer eventId){
        List<RunRace> result = new ArrayList<RunRace>();
        EventRunRace eRR = new EventRunRace();
        List<EventRunRace> allORaces = GeneralDAO.getDb().from(eRR).where(eRR.event_id).is(eventId).select();
        for(EventRunRace eRRNext: allORaces){
            List<RunRace> tempList = GeneralDAO.getDb().from(EMPTY_RUN_RACE).where(EMPTY_RUN_RACE.id).is(eRRNext.runrace_id).select();
            if(!tempList.isEmpty()){
                result.add(tempList.get(tempList.size()-1));
            }
        }
        return result;
    }

    public static List<Race> getAllRacesByEventId(Integer eventId){
        List<Race> result = new ArrayList<Race>();
        result.addAll(getAllRunRacesByEventId(eventId));
        result.addAll(getAllORacesByEventId(eventId));
        return result;
    }

    public static boolean updateEvent(Event event){
        List<Event> list = GeneralDAO.getDb().from(EMPTY_EVENT).where(EMPTY_EVENT.id).is(event.id).select();
        if(!list.isEmpty()){
            GeneralDAO.getDb().from(EMPTY_EVENT).where(EMPTY_EVENT.id).is(event.id).delete();
            addEvent(event);
            return true;
        }
        return false;
    }

    /**
     *
     * @return List of all events, where name and place contains String from request.
     */
    public static List<Event> speedSearch(String searchString) {
        Connection connection = GeneralDAO.getConnection();
        List<Event> result = new ArrayList<Event>();
        String sql = "SELECT * FROM EVENT WHERE (NAME LIKE ?) OR (PLACE LIKE ?) OR (NAME LIKE ? AND PLACE LIKE ?)";
        try {
            PreparedStatement statement = connection.prepareStatement(sql);
            ResultSet resultSet = statement.executeQuery();
            while (resultSet.next()) {
                int id = resultSet.getInt(1);
                int user_id = resultSet.getInt(2);
                String name = resultSet.getString(3);
                String place = resultSet.getString(4);
                Timestamp timestamp = resultSet.getTimestamp(5);
                Event event = new Event(id, user_id, name, place, timestamp);
                result.add(event);
            }
        } catch (SQLException ex) {
            Logger.getLogger(EventDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return result;
    }
}
